package com.gopay.common.manager.ptbank.impl;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.gopay.common.dao.ptbank.GsrtBankHfTdAcctSumDao;
import com.gopay.common.domain.ptbank.GsrtBankHfTdAcctSum;
import com.gopay.common.manager.ptbank.GsrtBankHfTdAcctSumManager;
import com.gopay.common.order.dao.CommonQueryDAO;

/**
 * 标的账务汇总文件 ManagerImpl
 * GsrtBankHfTdAcctSumManagerImpl.java
 * 2016年4月27日
 * @author jlj
 *  www.gopay.com.cn Inc.All rights reserved.
 */
@Service("gsrtBankHfTdAcctSumManager")
public class GsrtBankHfTdAcctSumManagerImpl implements GsrtBankHfTdAcctSumManager {

    @Resource(name = "gsrtBankHfTdAcctSumDao")
    private GsrtBankHfTdAcctSumDao gsrtBankHfTdAcctSumDao;

	@Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;
	
	private static final String QUERYSQL="select   i.gopay_order_id as proid,"
			+ "'' as mercustid,"
			+ " p.cust_name as borrname,"
			+ " p.cert_type as borrcerttype,"
			+ "p.cert_no as borrcertid,"
			+ "'' as creditacctno,"
			+ "i.bid_state as prostat,"
			+ "i.created_ts as ojtdate,"
			+ "'' as realdate,"
			+ "'' as prcp,"
			+ "'' as intst,"
			+ "'' as lastrepay,"
			+ "'' as realclosedate,"
			+ "'' as totalprcp,"
			+ "'' as totalintst,"
			+ "'' as repayprcp,"
			+ "'' as repayintst,"
			+ "'' as totalrepayprcp,"
			+ "'' as tatalrepayintst,"
			+ "'' as advmoney,"
			+ "'' as outadvmoney,"
			+ "'' as creditmoney,"
			+ "'' as totalcreditmoney"
			+ " from (select m.*, s.cust_id as cid from gsrt_cust_tender_master m "
			+ " left join gsrt_cust_signed s on m.borrower_constracter_id=s.constracter_id)i "
			+ " left join cust_person_info p on i.cid= p.cust_id ";
	
	private static final StringBuilder QUERY_HEADER = new StringBuilder()
			.append("select   i1.gopay_order_id as proid,")
			.append("'' as mercustid,")
			.append("p.cust_name as borrname,")
			.append("p.cert_type as borrcerttype,")
			.append("p.cert_no as borrcertid,")
			.append("'' as creditacctno,")
			.append("i1.bid_state as prostat,")
			.append("i1.mer_txn_tm as ojtdate,")
			.append("'' as realdate,")
			.append("i1.mer_txn_amt as prcp,")
			.append("'' as intst,")
			.append("i1.last_repay as lastrepay,")
			.append("'' as realclosedate,")
			.append("'' as totalprcp,")
			.append("'' as totalintst,")
			.append("i1.repayprcp as repayprcp,")
			.append("'' as repayintst,")
			.append("i1.totalrepayprcp  as totalrepayprcp,")
			.append("''as tatalrepayintst,")
			.append("'' as advmoney,")
			.append("'' as outadvmoney,")
			.append("'' as creditmoney,")
			.append("'' as totalcreditmoney ")
			.append(" from (")
			.append("select i0.*,sum(r.mer_txn_amt) as repayprcp, max(r.created_ts) as last_repay from ")
			.append("( ")
			.append("select m.*, s.cust_id as cid from ")
			.append("(")
			.append("select g.mer_cust_id,g.mer_txn_amt,g.bid_no,g.mer_txn_tm,g.gopay_order_id,g.bid_state,g.borrower_constracter_id,g.total_repayment_amt as totalrepayprcp")
			.append(" from gsrt_bids_info g ")
			.append(")m ")
			.append("left join gsrt_cust_signed s on m.borrower_constracter_id=s.constracter_id ")
			.append(")i0 ")
			.append("left join gsrt_repayment r on r.mer_cust_id= i0.mer_cust_id and r.bid_no=i0.bid_no ");
	
	private static final StringBuilder QUERY_DATE_CONDITION = new StringBuilder()
			.append(" where to_char(r.created_ts,'YYYYMMDD') = ");
	
	private static final StringBuilder QUERY_STATUS_CONDITION = new StringBuilder()
			.append(" r.txn_sta_cd= ");
	
	private static final StringBuilder QUERY_TAIL = new StringBuilder()
			.append(" group by i0.mer_cust_id,i0.bid_no,i0.mer_txn_tm,i0.gopay_order_id,i0.bid_state,i0.mer_txn_amt, i0.borrower_constracter_id,i0.cid,i0.totalrepayprcp ")
			.append(" )i1 ")
			.append("  left join cust_person_info p on i1.cid= p.cust_id ");
	@Override
	public List<GsrtBankHfTdAcctSum> getDtlListByDate(Date date) {
		/*String sql = QUERYSQL +
				"where to_char(i.created_ts,'YYYYMMDD') = " + date;*/
		SimpleDateFormat dd = new SimpleDateFormat("yyyyMMdd");
		String queryDate = dd.format(date);
		String sql = new StringBuilder()
				.append(QUERY_HEADER)
				.append(QUERY_DATE_CONDITION)
				.append("'")
				.append(queryDate)
				.append("'")
				.append(" and ")
				.append(QUERY_STATUS_CONDITION)
				.append(" '20000' ")
				.append(QUERY_TAIL)
				.toString();
		List<Object[]> list = commonQueryDAO.findBySql(sql);
		List<GsrtBankHfTdAcctSum> sumList= new ArrayList<GsrtBankHfTdAcctSum>();
		for (Object[] object : list) {
			GsrtBankHfTdAcctSum sum = new GsrtBankHfTdAcctSum();
			sum.setProid(object[0]==null ? "" : object[0].toString());
			sum.setMercustid(object[1]==null ? "" : object[1].toString());
			sum.setBorrname(object[2]==null ? "" : object[2].toString());
			sum.setBorrcerttype(object[3]==null ? "" : object[3].toString());
			sum.setBorrcertid(object[4]==null ? "" : object[4].toString());
			sum.setCreditacctno(object[5]==null ? "" : object[5].toString());
			sum.setProstat(object[6]==null ? "" : object[6].toString());
			sum.setOjtdate(object[7]==null ? "" : object[7].toString());
			sum.setRealdate(object[8]==null ? "" : object[8].toString());
			sum.setPrcp(new BigDecimal(object[9] == null ? "0.00" : object[9].toString()));
			sum.setIntst(new BigDecimal( object[10] == null ? "0.00" : object[10].toString()));
			sum.setLastrepay(object[11]==null ? "" : object[11].toString());
			sum.setRealclosedate(object[12]==null ? "" : object[12].toString());
			sum.setTotalprcp(new BigDecimal(object[13] == null ? "0.00" : object[13].toString()));
			sum.setTotalintst(new BigDecimal(object[14] == null ? "0.00" : object[14].toString()));
			sum.setRepayprcp(new BigDecimal(object[15] == null ? "0.00" : object[15].toString()));
			sum.setRepayintst(new BigDecimal(object[16] == null ? "0.00" : object[16].toString()));
			sum.setTotalrepayprcp(new BigDecimal(object[17] == null ? "0.00" : object[17].toString()));
			sum.setTatalrepayintst(new BigDecimal(object[18] == null ? "0.00" : object[18].toString()));
			sum.setAdvmoney(new BigDecimal(object[19] == null ? "0.00" : object[19].toString()));
			sum.setOutadvmoney(new BigDecimal(object[20] == null ? "0.00" : object[20].toString()));
			sum.setCreditmoney(new BigDecimal(object[21] == null ? "0.00" : object[21].toString()));
			sum.setTotalcreditmoney(new BigDecimal(object[22] == null ? "0.00" : object[22].toString()));
		
			sumList.add(sum);
		}
		return sumList;
	}
}
